package com.czy.utils.office;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.handler.impl.ExcelDataHandlerDefaultImpl;
import cn.afterturn.easypoi.handler.inter.IExcelDataHandler;
import com.czy.common.exception.CustomException;
import com.czy.utils.office.vo.ExcelSheet3;
import com.czy.utils.office.vo.StudentEntity;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * excel操作工具类
 * @author czy
 * @date 2022-2-24 21:32
 */
public class ExcelUtils {
    public static void main(String[] args) throws FileNotFoundException {
//        InputStream inputStream = ExcelUtils.class.getResourceAsStream("/office/excel/导入测试.xlsx");
//        // 1.读取第一个sheet页
//        List<StudentEntity> sheet1List = importExcel(inputStream, StudentEntity.class,buildImportParams(0,1));
//        sheet1List.stream().forEach(System.out::println);
//        // 2.读取第二个sheet页
//        List<StudentEntity> sheet2List = importExcel(inputStream, StudentEntity.class,buildImportParams(0,1,1,1));
//        sheet2List.stream().forEach(System.out::println);
//        // 3.一次读取多个sheet页(读取第一个sheet和第二个sheet)
//        List<StudentEntity> sheet_1_2_List = importExcel(inputStream, StudentEntity.class,buildImportParams(0,1,0,1));
//        sheet_1_2_List.stream().forEach(System.out::println);

        // 4.map 方式导入
//        ImportParams importParams = buildImportParams(0, 1);
//        String [] importFileds=new String[]{"姓名","性别22"};
//        importParams.setImportFields(importFileds);
//        importParams.setDataHandler(new ExcelDataHandlerDefaultImpl<Map<String,Object>>(){
//            @Override
//            public void setMapValue(Map<String, Object> map, String originKey, Object value) {
//                map.put(originKey,value);
//            }
//        });
//        List<Map> sheet1List2 = importExcel(inputStream, Map.class,importParams);
//        for (Map map : sheet1List2) {
//            Set<String> set = map.keySet();
//            for (String key : set) {
//                System.out.println(key+"=="+map.get(key));
//            }
//
//        }

        // 5.多表头导入
        InputStream inputStream = new FileInputStream(new File("D:\\file\\export.xlsx"));
        ImportParams importParams = buildImportParams(0, 2);
        List<ExcelSheet3> excelSheet3s = importExcel(inputStream, ExcelSheet3.class, importParams);
        excelSheet3s.stream().forEach(System.out::println);

//        OutputStream outputStream = new FileOutputStream(new File("D:\\file\\export.xlsx"));
//        // 导出
//        List<ExcelSheet3> list = new ArrayList<>();
//        ExcelSheet3 build = ExcelSheet3.builder().name("aaa").tel("123").footballl("是").basketball("否").build();
//        list.add(build);
//        exportExcel(list,ExcelSheet3.class,outputStream,buildExportParams());

    }

    public static ImportParams buildImportParams(int titleRows, int headerRows,int startSheetIndex,int sheetNum){
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(titleRows);//表格标题行数,默认0
        importParams.setHeadRows(headerRows);//表头行数,默认1
        importParams.setStartSheetIndex(startSheetIndex);//开始读取的sheet位置,默认为0
        importParams.setSheetNum(sheetNum);//上传表格需要读取的sheet 数量,默认为1
        importParams.setImportFields(new String[]{});//导入时校验数据模板,是不是正确的Excel 缺少必传字段则不允许导入
        importParams.setVerifyHandler(null);//设置校验处理接口

        return importParams;
    }

    /**
     * 读取第一个sheet页
     * @param titleRows
     * @param headerRows
     * @return
     */
    public static ImportParams buildImportParams(int titleRows, int headerRows){
        return buildImportParams(titleRows,headerRows,0,1);
    }

    public static <T> List<T> importExcel(InputStream inputStream, Class<T> pojoClass,ImportParams importParams) {
        try {
            return ExcelImportUtil.importExcel(inputStream,pojoClass,importParams);
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomException(e.getMessage());
        }
    }

    public static ExportParams buildExportParams(){
        ExportParams exportParams = new ExportParams();
//        exportParams.setTitle("标题一");// 设置标题
//        exportParams.setSecondTitle("二级标题");// 二级标题
//        exportParams.setSheetName("sheet名称");// 设置sheet名称
        exportParams.setType(ExcelType.XSSF);// excel 格式 XSSF 2007  HSSF 2003
        return exportParams;
    }

    public static void exportExcel(List<?> list, Class<?> pojoClass, OutputStream outputStream,ExportParams exportParams){
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
